# %load_ext pretty_jupyter
import pandas as pd
import plotly.graph_objects as go
from plotly.offline import init_notebook_mode
init_notebook_mode()
import cpi
# cpi.update()
cpi_data = {year: cpi.inflate(1, year) for year in range(1970, 2024)}
def inflate(money):
''' Helper function because cpi function is slow '''
return money*pd.Series(data=money.index, index=money.index).map(cpi_data)
def custom_formatter(value):
''' Helper function for formatting numbers in tables '''
return '{:,.1%}'.format(value) if type(value) != str else value
def vis_array(which, traces):
''' Helper function for making array of booleans needed for switching between
different display options in plots'''
if which == 1:
return [True] * traces + [False] * (2 * traces)
if which == 2:
return [False] * traces + [True] * traces + [False] * traces
if which == 3:
return [False] * (2 * traces) + [True] * traces
return None
######################
#
# Data Import
#
######################
#################
# CCD Fiscal Data
#################
# fiscal_codes is for pretty printing in the charts
fiscal_codes = pd.read_csv('data/ccd_fiscal/fiscal_codes.csv',
header=None,
names=['code',
'numeric',
'fdsa',
'Category'],
index_col='code')
fiscal_codes.loc['OTHER', :] = ('AN',0,'Other')
fiscal_codes.loc['Instruction_Other', :] = ('AN',0,'Instruction Expenditure Other<a href="#fn6"><sup>6</sup></a> ')
utah = (pd.read_csv('data/ccd_fiscal/data/fiscal.csv')
.set_index(['SURVYEAR', 'STABR'])
.apply(pd.to_numeric, errors='coerce', axis=0)
.xs('UT', level='STABR')
.apply(inflate, axis=0)
)
x = utah.index
########################
# Nonfiscal - student enrollment info
########################
membership = pd.read_csv('data/ccd_nonfiscal_new/membership.csv',
index_col=[0, 1],
header=[0, 1, 2])
utah_membership = membership.loc[(slice(None), 'UT'), ]
total_enrollment = pd.to_numeric(utah_membership.loc[:, ('No Category Codes', 'No Category Codes', 'No Category Codes')])
We saw in the previous document that education spending has been increasing for years while academic achievement has seen essentially no improvement. If the state of Utah was spending the same per student as we were in 1989, we'd be saving about $3 billion1 per year (visualized in Figure 1). That's greater that 1% of Utah's GDP. This begs the question: if spending has risen so dramatically, and if academic achievement has largely been unaffected, how is this money being spent?
To begin with the conclusions: spending increases have occurred in most sectors of the education system but the areas that have seen the greatest growth include: construction, student support services, instructional supplies and purchased services, and benefit costs. Surprisingly, while technically there has been an increase in "Instruction Salary" expenditures, there isn't any explicit evidence of an increase in spending on teacher salaries.
te = total_enrollment.droplevel('STABR')
te.name=('total enrollment')
df = utah[['TE11']].join(te)
# Calculate the spending per student
df['Total Spending per Student'] = df['TE11'] / df['total enrollment']
# Calculated the hypothetical total spending at constant spending per student
df['hypo_spend'] = df['total enrollment'] * df.loc[1989, 'Total Spending per Student']
df['hypo_spend_per_student'] = df.loc[1989, 'Total Spending per Student']
# Remove observations before 1989
df = df.loc[1989:, :]
fig = go.Figure()
# Add absolute value traces
fig.add_trace(go.Scatter(x=df.index, y=df['TE11'],
mode='lines',
name='Total Spending (actual)',
visible=True))
fig.add_trace(go.Scatter(x=df.index, y=df['hypo_spend'],
mode='lines',
name='Total Spending (at 1989 per student rate)',
visible=True))
# Add per-student values trace
fig.add_trace(go.Scatter(x=df.index, y=df['Total Spending per Student'],
mode='lines',
name='Total Spending per Student (actual)',
visible=False))
fig.add_trace(go.Scatter(x=df.index, y=df['hypo_spend_per_student'],
mode='lines',
name='Total Spending per Student (at 1989 per student rate)',
visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(1,2)},
{"title": "Figure 1: Total Spending"}]),
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(2,2)},
{"title": "Figure 1: Total Spending per Student"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 1: Total Spending",
title_font_size=24,
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
To draw these conclusions, I've used data from the National Center for Education Statistics's (NCES) Common Core of Data (CCD) surveys. These are surveys that district and state education agencies report to the NCES each year which contain fiscal, enrollment, and staffing information. Most of the data of present interest is available from 1989 to 2022 but a few variables were only reported beginning in 2004.
All of the dollar amounts presented here are inflation adjusted to 2024 dollars and, generally speaking, I'll be looking at changes in per-student spending to adjust for student enrollment. I think, in this context at least, inflation adjusted, per-student spending is the measure that really gets at the heart of the matter.
Spending Overview¶
The CCD subdivides expenditures into a hierarchical scheme with 5 main categories at the top level: Construction, Support Services, Instruction, Noninstructional Services, and Other. A few clarifications on these categories:
- Construction is termed "Facilities Acquisition Nonproperty" in the CCD dataset and is defined as "expenditures for the construction and renovation of all school and other facilities"2.
- "Noninstruction Services" is almost entirely made up by food services.
- "Other" is comprised of three unrelated but small categories in the CCD dataset: "Community Service Non-property", "Direct Cost Program Subtotal", and "Property Total"3.
In this document I'll present the following two items at each level of expenditure. First, a chart showing the percentage growth in per student spending (5 year rolling average) of each category from 1993 to 2022. Second, an interactive graphic showing spending by category over time. This graphic has a dropdown menu where the spending can be presented as per student, absolutely, and as a percentage of whatever level is being presented.
utah['OTHER'] = utah.loc[:, ['E81', 'STE9', 'TE10']].sum(axis=1)
level_top = utah.loc[1989:, ['STE1', 'STE2T', 'STE3', 'E61', 'OTHER']].div(total_enrollment,
axis=0)
percent_changes_top_level = (
level_top.loc[2018:2022,:].mean() / level_top.loc[1989:1993,:].mean()) - 1
table_top_level = (fiscal_codes['Category'].to_frame()
.join(percent_changes_top_level.to_frame('% Growth'),
how='right')
.sort_values(by='% Growth', ascending=False)
)
table_top_level.columns = pd.MultiIndex.from_product([['% Growth in Per Student \
Spending<a href="#fn4"><sup>4</sup></a> by \
Main Category (1993 to 2022)'],
table_top_level.columns])
table_top_level.style.hide().format(formatter=custom_formatter)
| % Growth in Per Student Spending4 by Main Category (1993 to 2022) | |
|---|---|
| Category | % Growth |
| Facilities Acquisition Nonproperty | 950.3% |
| Support Expenditures Total Support Services | 80.2% |
| Instructional Expenditures Subtotal | 54.1% |
| Other | 7.2% |
| Noninstructional Services Total | 6.4% |
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['STE1'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE2T'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE3'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Noninstruction', visible=True))
fig.add_trace(go.Scatter(
x=x, y=(utah['E81'] + utah['STE9'] + utah['TE10']) / total_enrollment,
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other',
visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E61'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=True))
# Traces for absolute levels of spending
fig.add_trace(go.Scatter(
x=x, y=utah['STE1'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE2T'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE3'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Noninstruction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['E81'] + utah['STE9'] + utah['TE10']),
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other',
visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E61'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE1'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE2T'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE3'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Noninstruction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['E81'] + utah['STE9'] + utah['TE10']) / utah['TE11'],
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other',
visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E61'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}]),
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 5)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 2: Total Spending by Main Category",
title_font_size=24,
xaxis_range=(1989, 2022),
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
Immediately, we can see that Construction expenditures have absolutely skyrocketed! So much so that it looks like an error. In contrast, Noninstruction (food services) and those categories in "Other" don't seem to be playing a part in the increase in spending.
Instruction and Support expenditures have increased at a relatively slow rate compared to construction but we can see in Figure 2 that they make up the lion's share of the total budget (82.9% in 2022). Consequently, even though it may appear that the percentage increases have been modest in these two categories, their large size makes them significant contributing factors to the overall growth in spending.
Below, I'll be digging a little deeper into each of these "problem" categories: Construction, Support, and Instruction. The CCD dataset provides subdivisions of the Support and Instruction categories which will provide us with more precision in finding sources of spending increases.
Construction¶
One thing I noticed (and you can see in Figure 2) is that construction spending exploded in the 90s. In fact, from 1989 to 1992, construction spending averaged only 1.2% of total spending but by 2000 it was at 9.6% and would never fall below 6% again. Construction, in my opinion, is arguably the largest and most obvious culprit for education spending increases in Utah. Even over the last decade, when increases in enrollment have slowed down to essentially zero, construction expenses have been at all time highs. If Utah spent as much, per student, on construction in 2022 as in 1989, we would've saved \$512 million a year.
I have many unanswered questions concerning construction spending given that it was so low in the late 80s and early 90s. Was that period anomalous? If not, what's caused the increase in spending? Is it the price of labor? Material costs? (A little reminder that the figures I've presented are all inflation adjusted to 2024 dollars). Has legislation been passed requiring fundamentally more expensive buildings? The documentation of building codes for facility construction seems... lengthy... and has grown over time with the adoption of new regulations.
A deep dive on the cause(s) of increased construction spending is outside the scope of this report but I think it warrants future inspection.
Support¶
Support is a large expenditure category and, since 1989, has been the second fastest growing after construction. It includes many subcategories but generally represents expenses for
activities which support instruction. These services include school building operation and maintenance, school administration, student support services, student transportation, instructional staff support, school district administration, business services, research, testing, and data processing.5
The CCD dataset subdivides Support expenditures by one of two schemes: by service type and by expense type. In the first scheme, the subcategories of service type are:
level_support = utah.loc[1989:, ['STE22', 'STE23', 'STE24', 'STE25',
'STE26', 'STE27', 'STE28']].div(total_enrollment,
axis=0)
percent_changes_support = (
level_support.loc[2018:2022,:].mean() / level_support.loc[1989:1993,:].mean()) - 1
table_support_level = (fiscal_codes['Category'].to_frame()
.join(percent_changes_support.to_frame('% Growth'),
how='right')
.sort_values(by='% Growth', ascending=False)
).rename({'Category': 'Service Type'}, axis=1)
table_support_level['Service Type'] = (table_support_level['Service Type']
.apply(lambda x: ' '.join(x.split()[3:])))
table_support_level.columns = pd.MultiIndex.from_product([['% Growth in Per Student \
Spending<a href="#fn4"><sup>4</sup></a> \
by Support Service Type (1993 to 2022)'],
table_support_level.columns])
table_support_level.style.hide().format(formatter=custom_formatter)
| % Growth in Per Student Spending4 by Support Service Type (1993 to 2022) | |
|---|---|
| Service Type | % Growth |
| Student Support Services | 222.3% |
| Other Services | 138.7% |
| Instructional Staff Support | 89.7% |
| School Administration | 81.6% |
| General Administration | 66.0% |
| Pupil Transportation | 55.2% |
| Operation & Maintenance | 36.5% |
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['STE26'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Operations and Maintenance', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE27'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Transportation', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE24'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='General Admin', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE25'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='School Admin', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE23'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Instruction Support Services', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE28'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other',visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['STE22'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Support Services', visible=True))
# Traces for absolute levels spending
fig.add_trace(go.Scatter(
x=x, y=utah['STE26'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Operations and Maintenance', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE27'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Transportation', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE24'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='General Admin', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE25'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='School Admin', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE23'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Instruction Support Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE28'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE22'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Support Services', visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE26'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Operations and Maintenance', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE27'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Transportation', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE24'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='General Admin', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE25'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='School Admin', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE23'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Instruction Support Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE28'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE22'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Student Support Services', visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 7)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 7)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}]),
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 7)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 3: Support Spending by Service Type",
title_font_size=24,
xaxis_range=(1989, 2022),
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
All service type subcategories grew but none stick out particularly with the exception of maybe Student Support Services. Taking that subcategory as an example, Student Support spending grew from \$180 per student in 1989 to \$572 in 2022 (again, these are 2024 dollars). Student Support Services are represented in Figure 3 as the top colored band. You can see that it's width grows pretty consistently and significantly from left to right. I was a little confused about what services are included in "Student Support" but apparently it includes expenses for "attendance and social work, guidance, health, psychological services, speech pathology, audiology, and other student support services"3. If Utah Student Support expenditures per student were the same in 2022 as in 1989, Utah would have saved about \$270 million per year.
The CCD also breaks down Support expenditures by expenditure type in the following scheme:
level_support_2 = utah.loc[1989:, ['TE21', 'TE22', 'TE23', 'TE24',
'TE26', ]].div(total_enrollment,
axis=0)
percent_changes_support_2 = (
level_support_2.loc[2018:2022,:].mean() / level_support_2.loc[1989:1993,:].mean()) - 1
table_support_level_2 = (fiscal_codes['Category'].to_frame()
.join(percent_changes_support_2.to_frame('% Growth'),
how='right')
.sort_values(by='% Growth', ascending=False)
).rename({'Category': 'Expenditure Type'}, axis=1)
table_support_level_2['Expenditure Type'] = (table_support_level_2['Expenditure Type']
.apply(lambda x: ' '.join(x.split()[2:])))
table_support_level_2.columns = pd.MultiIndex.from_product([['% Growth in Per Student \
Spending<a href="#fn4"><sup>4</sup></a> by \
Support Expenditure Type (1993 to 2022)'],
table_support_level_2.columns])
table_support_level_2.style.hide().format(formatter=custom_formatter)
| % Growth in Per Student Spending4 by Support Expenditure Type (1993 to 2022) | |
|---|---|
| Expenditure Type | % Growth |
| Employee Benefits Subtotal | 133.1% |
| Purchased Services Subtotal | 81.3% |
| Salaries Subtotal | 75.7% |
| Supplies Subtotal | 41.8% |
| Other Subtotal | -43.5% |
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['TE21'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Salaries', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['TE22'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Benefits', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['TE23'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Purchased Services', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['TE24'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Supplies', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['TE26'] / total_enrollment, mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other',visible=True))
# Traces for absolute levels spending
fig.add_trace(go.Scatter(
x=x, y=utah['TE21'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Salaries', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['TE22'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Benefits', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['TE23'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Purchased Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['TE24'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Supplies', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['TE26'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other', visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['TE21'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Salaries', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['TE22'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Benefits', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['TE23'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Purchased Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['TE24'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Supplies', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['TE26'] / utah['STE2T'], mode='lines', stackgroup='one', line=dict(width=0.5),
name='Other', visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}]),
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 5)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 4: Support Spending by Expenditure Type",
title_font_size=24,
xaxis_range=(1989, 2022),
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
The absolute size of Support Benefits and Support Salaries makes them large sources of spending increases. If Utah spent as much per student on Support Benefits in 2022 as in 1989, then about \$305 million would be saved a year. Similarly, for Support Salaries \$504 million would be saved a year. The only subcategory to decrease in this scheme is the "Other" category which is by far the smallest, accounting for less that 1% of Support spending every year since 1992.
It's possible to hone in on individual sub-subcategories like Benefits for Student Support Services (grew from \$14 million in 1989 to \$100 million in 2022) or Supplies for School Administration (grew from \$551,734 in 1989 to \$7.4 million 2022). And I suppose that would be useful from a managerial standpoint but I think that misses a bigger picture. While Student Support services saw standout growth as noted above, everything else has been growing together in concert. And that's resulted in a large total increase in Support expenditures. If Utah was spending the same on Support Services, per student, in 2022 as in 1989, we'd have saved \$961 million per year. Yeah, just shy of $1 billion. I think this warrants a serious conversation about what that spending is actually accomplishing.
Instruction¶
Instruction is the largest expenditure category, accounting for at least 50% of expenses every year (see Figure 1 for details). It's percentage growth has been significantly less than Construction and Support but because it's such a large source of spending, Instruction is the largest contributor to total expense growth. If Utah spent the same per student on Instruction in 2022 as it did in 1989, we would have saved $1.56 billion a year. That's roughly half of total expense growth since 1989. But there's a twist: it's not exactly clear where these increases in spending are going and, in particular, it's not clear that these increases in spending are being used for explicitly academic purposes.
utah['Instruction_Other'] = utah[['E14', 'E15', 'E18']].sum(axis=1)
table_3 = (
utah.loc[:, ['E11', 'E12', 'E13', 'E16', 'Instruction_Other']]
.div(total_enrollment, axis=0)
.rolling(window=5).mean()
.pipe(lambda d: d.loc[2022] / d.loc[1993] - 1)
.loc['UT'].to_frame()
.join(
fiscal_codes['Category']
.apply(lambda x: ' '.join(x.split()[2:]))
)
.rename(columns={'UT': '% Growth', 'Category': 'Expenditure Type'})
.loc[:, ['Expenditure Type', '% Growth']]
.sort_values(by='% Growth', ascending=False)
)
table_3.columns = pd.MultiIndex.from_product([['% Growth in Per Student \
Spending<a href="#fn4"><sup>4</sup></a> \
by Instruction Expenditure Type (1993 to 2022)'],
table_3.columns])
table_3.style.hide().format(formatter=custom_formatter)
| % Growth in Per Student Spending4 by Instruction Expenditure Type (1993 to 2022) | |
|---|---|
| Expenditure Type | % Growth |
| Purchased Services | 224.0% |
| Supplies | 173.5% |
| Employee Benefits | 79.7% |
| Salaries | 34.6% |
| Other6 | -3.5% |
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salaries', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E12'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Benefits', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E16'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Purchased Services', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E13'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Supplies', visible=True))
fig.add_trace(go.Scatter(
x=x, y=(utah['E14'] + utah['E18']) / total_enrollment,
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other',
visible=True))
# Traces for absolute levels of spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salaries', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E12'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Benefits', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E16'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Purchased Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E13'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Supplies', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['E14'] + utah['E18']),
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other',
visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11'] / utah['STE1'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salaries', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E12'] / utah['STE1'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Benefits', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E16'] / utah['STE1'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Purchased Services', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E13'] / utah['STE1'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Supplies', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['E14'] + utah['E18']) / utah['STE1'],
mode='lines', line=dict(width=0.5), stackgroup='one', name='Other<a href="#fn7"><sup>7</sup></a>',
visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 5)},
{"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}]),
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 5)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 5: Instruction Spending by Expenditure Type",
title_font_size=24,
xaxis_range=(1989, 2022),
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
The percentage growth in per student spending for each Instruction subcategory is roughly inversely related to its size. While Instruction Supplies and Purchased Services are small in size, they are two of the fastest growing subcategories of the entire CCD dataset. If Utah was spending the same on these sub categories in 2022 as it did in 1989, we'd have savings of \$394 million per year. And what is Utah getting for that money? The definition of Purchased Services is pretty vague: "expenditures for professional and technical services and the renting of equipment"6 and the definition of Instruction Supplies is similar:
items that are consumed, wear out, or deteriorate through use, or items that lose their identity through fabrication or incorporation into different or more complex units or substances. These include expenditures for general supplies; paper and other materials required for printing and copying; and books, periodicals, and reference materials.6
In 2022, Utah spent \$578.96 per student on Instruction Supplies. That sounds really high to me. I'm curious to see how that's being spent.
Opposite to Supplies and Purchased Services, the Instruction Salary sub category has grown rather slowly with essentially no per student growth after 2000. This seems consistent with teacher salary figures from this source where we see a jump in teacher pay from 1990 to 2000. While we can't subdivide the Salary subcategory all the way back to 1989, we can do it back to 2004:
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11A'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='General Program', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E11B'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Special Ed', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E11C'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Vocational', visible=True))
fig.add_trace(go.Scatter(
x=x, y=utah['E11D'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Other', visible=True))
fig.add_trace(go.Scatter(
x=x, y=(utah['E11'] - utah[['E11A', 'E11B', 'E11C', 'E11D']].sum(axis=1)) / total_enrollment,
mode='lines', line=dict(width=0.5), stackgroup='one', name='Undocumented',
visible=True))
# Traces for absolute levels of spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11A'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='General Program', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E11B'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Special Ed', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E11C'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Vocational', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E11D'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Other', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['E11'] - utah[['E11A', 'E11B', 'E11C', 'E11D']].sum(axis=1)),
mode='lines', line=dict(width=0.5), stackgroup='one', name='Undocumented',
visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11A'] / utah['E11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='General Program', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11B'] / utah['E11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Special Ed', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11C'] / utah['E11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Vocational', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11D'] / utah['E11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Other', visible=False))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['E11'] - utah[['E11A', 'E11B', 'E11C', 'E11D']].sum(axis=1)) / utah['E11'],
mode='lines', line=dict(width=0.5), stackgroup='one', name='Undocumented',
visible=False))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 5)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 5)},
{"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}]),
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 5)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 6: Instruction Salaries",
title_font_size=24,
xaxis_range=(2004, 2022),
yaxis_title="2024 dollars",
yaxis_tickprefix="$",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
This figure is both illuminating and disturbing to me. Since 2004, per student spending on regular programming (the stuff you think of when you think of school: reading, math, social studies, ...) has actually decreased. And so has per student spending on special ed and vocational instruction. Moreover, what would be included in the "Other" instruction salary subcategory? Perhaps it's just accounting jui-jitsu, but there was an enormous increase in Instruction salaries labeled "Other" in 2019 and a similar drop in spending for salaries for General Programming. I suspect there was a reclassification of salaries in 2019 from General Programming to Other. But even since 2019, the Other salary subcategory has continued to grow quickly.
Of potentially greater concern, the four salary subcategories (General Programming, Special Ed, Vocational, and Other) sum to the reported Instruction Salary expenditure subtotal in only one year (2010). This discrepancy is shown in Figure 6 as the top colored band, labeled "Undocumented". I've reached out to a few employees at the Utah State Board of Education but haven't received any clarification as of Jan 7, 2025.
Lastly, it really surprised me when I compared "Regular Programming Salary" to all the other expenses. In 2022, only 18.6% of state expenditures for education went to regular programming teacher salary. Shown here in Figure 7:
fig = go.Figure()
# Traces for per student spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11A'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='RegProg Salary', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['E11'] - utah['E11A'].fillna(0)) / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salary less RegProg', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['STE1'] - utah['E11']) / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction less Salary', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE2T'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E61'] / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['TE11'] - utah[['STE1', 'STE2T', 'E61']].sum(axis=1)) / total_enrollment, mode='lines', line=dict(width=0.5),
stackgroup='one', name='Everything Else', visible=False))
# Traces for absolute levels of spending
fig.add_trace(go.Scatter(
x=x, y=utah['E11A'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='RegProg Salary', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['E11'] - utah['E11A'].fillna(0)), mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salary less RegProg', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['STE1'] - utah['E11']), mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction less Salary', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['STE2T'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=False))
fig.add_trace(go.Scatter(
x=x, y=utah['E61'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=False))
fig.add_trace(go.Scatter(
x=x, y=(utah['TE11'] - utah[['STE1', 'STE2T', 'E61']].sum(axis=1)), mode='lines', line=dict(width=0.5),
stackgroup='one', name='Everything Else', visible=False))
# Traces for levels as percentage of total
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E11A'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='RegProg Salary', visible=True))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['E11'] - utah['E11A'].fillna(0)) / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Salary less RegProg', visible=True))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['STE1'] - utah['E11']) / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Instruction less Salary', visible=True))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['STE2T'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Support', visible=True))
fig.add_trace(go.Scatter(
x=x, y=100 * utah['E61'] / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Construction', visible=True))
fig.add_trace(go.Scatter(
x=x, y=100 * (utah['TE11'] - utah[['STE1', 'STE2T', 'E61']].sum(axis=1)) / utah['TE11'], mode='lines', line=dict(width=0.5),
stackgroup='one', name='Everything Else', visible=True))
# Add dropdown
fig.update_layout(
updatemenus=[
dict(
active=0,
buttons=[
dict(label="Total Spending Percentages",
method="update",
args=[{"visible": vis_array(3, 6)},
{"yaxis.title.text": "Percent",
"yaxis.tickprefix": "",
"yaxis.ticksuffix": "%",
"yaxis.range": [0,100]},
{"title": "Figure 2: Total Spending Percentages"}]),
dict(label="Total Spending per Student",
method="update",
args=[{"visible": vis_array(1, 6)},
{"yaxis.title.text": "2024 dollars",
"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending per Student"}]),
dict(label="Total Spending",
method="update",
args=[{"visible": vis_array(2, 6)},
{"yaxis.tickprefix": "$",
"yaxis.ticksuffix": "",
"yaxis.autorange": True},
{"title": "Figure 2: Total Spending"}])
],
x=1,
y=1.03,
xanchor="right",
yanchor="bottom"
)
]
)
fig.update_layout(
title="Figure 7: Total Spending - Special Breakdown",
title_font_size=24,
xaxis_range=(1989, 2022),
yaxis_range=(0, 100),
yaxis_title="2024 dollars",
yaxis_ticksuffix="%",
legend=dict(orientation="h",
xanchor="center",
x=0.5),
margin_pad=5,
template="plotly_white"
)
fig.add_shape(type="rect",
xref="paper", yref="paper",
x0=0, y0=0, x1=1, y1=1,
line=dict(
color="grey",
width=1,
)
)
fig.show()
Moral of the Story¶
I've mulled over a few different hypotheses for why academic achievement hasn't improved over the last few decades but currently I'm finding myself most comfortable with this theory: the reason math and reading scores aren't improving is simply because regular programming is not a financial priority. We're spending more money on nicer buildings (construction), nonacademic causes (student support services and support generally), more on benefits (likely because of increased healthcare costs), instructional expenditures that don't go to teachers (supplies and purchased services), etc... One of the only things that we're not spending more money on is instructional salaries for academic programs.
Teacher pay is a topic for another report, but given what I've seen so far, it actually seems UNsurprising that academic achievement has been flat.
Notes¶
1 All dollar amounts referenced in this document are inflation adjusted to Jan 7, 2025 per the Consumer Price Index, Western Region: https://www.bls.gov/regions/west/cpi-summary/ro9xg01a.htm
2 https://nces.ed.gov/ccd/bat/glossary.asp?letter=F
3 See CCD documentation for details: https://nces.ed.gov/ccd/pdf/2024302_FY22_NPEFSDocumentation.pdf
4 % Growth in inflation adjusted, 5 year rolling average, per student spending from 1993 to 2022
5 https://nces.ed.gov/pubs99/condition99/pdf/glossary.pdf
6 The "Other" subcategory of Instruction is comprised of two CCD defined categories: "Instructional Expenditures Tuition to Private and Out-Of-State Schools" and "Instructional Expenditures Other". See CCD documentation for details: https://nces.ed.gov/ccd/pdf/2024302_FY22_NPEFSDocumentation.pdf
7 I have no explanation for why the subcategories don't sum up to 100% in 1994.